﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessManualEntry
// 文件功能描述：手工录入数据访问层
//
// 
// 创建标识：2012-03-27 王冲
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DbAdapter.sqldbAdapter;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    public class DataAccessManualEntry
    {
        /// <summary>
        /// 获取所有手工录入的数量
        /// </summary>
        /// <returns></returns>
        public int GetManualEntryCount()
        {
            sqldb db = new sqldb();
            string query = "select count(1) from V_CON_INPUT_VIEW_NEW_WC";
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }

        /// <summary>
        /// 获取所有手工录入的数量
        /// </summary>
        /// <returns></returns>
        public int GetManualEntryCount(string where)
        {
            sqldb db = new sqldb();
            string query = "select count(1) from V_CON_INPUT_VIEW_NEW_WC " + where;
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }

        /// <summary>
        /// 获取食堂名称列表（用于绑定下拉框）
        /// </summary>
        /// <returns></returns>
        public DataSet GetMacID()
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select b.MacID,a.Nodename from Yax_node a inner join Yax_Machine b on a.NodeID=b.NodeID " +
                               "where a.ConFlag=1 and b.MacDescribe='预留手工录入'";
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        /// <summary>
        /// 获取餐次(用于绑定下拉框)
        /// </summary>
        /// <returns></returns>
        public DataSet GetPeriodCode()
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select * from CON_PERIOD WHERE UseFlag=1";
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        /// <summary>
        /// 获取名称(用于绑定下拉框)
        /// </summary>
        /// <returns></returns>
        public DataSet GetDepositTypeID()
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select * from Con_DepositType  WHERE  DepositTypeID>100 and DepositName='清单'"+
                               "union all "+
                               "select * from Con_DepositType  WHERE  DepositTypeID>100 and DepositName='招待券'"+
                               "union all "+
                               "select * from Con_DepositType  WHERE  DepositTypeID>100 and DepositName='包房'"+
                               "union all "+
                               "select * from Con_DepositType  WHERE  DepositTypeID>100 and DepositName not in('清单','招待券','包房')";
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        /// <summary>
        /// 获取手工录入的列表（分页）
        /// </summary>
        /// <returns></returns>
        public DataSet SelManualEntryInfo(int pagesize, int startindex, string strquery)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                int startrow = pagesize * (startindex - 1);
                int endrow = pagesize * startindex + 1;
                string query = "select * from (select ROW_NUMBER() OVER(ORDER BY RDATETIME DESC) rownumber,* " +
                               "from V_CON_INPUT_VIEW_NEW_WC " + strquery + ") B "
                             + "where B.rownumber > " + startrow.ToString() + " and B.rownumber < " + endrow.ToString();
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        /// <summary>
        /// 保存（新增）
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int SaveManualEntryAdd(object[] obj)
        {
            sqldb db = new sqldb();
            int iPid = Convert.ToInt32(db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, "select b.PID from Con_DepositType a inner join FILE_PERSONNEL_VIEW b on a.DepositName=b.PName where a.DepositTypeID=" + obj[3].ToString()));
            //select BeginTime from CON_PERIOD where PeriodCode
            string time = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, "select BeginTime from CON_PERIOD where PeriodCode=" + obj[2]).ToString();
            string datetime = Convert.ToDateTime(obj[0]).ToString("yyyy-MM-dd") + " " + time;
            object objprice = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, "select SUM(MenuPrice) from Con_Menus where MenuId in(" + obj[6] + ")");
            decimal dconmoney = Convert.ToDecimal(obj[4]) * Convert.ToDecimal(objprice);
            string query = "insert into Con_Record(PID,RDateTime,ConMoney,MacID,DealMark,OperatorID,Count,Price) Values('" +
                iPid + "','" + datetime + "','" + Convert.ToInt32(dconmoney * 100) + "','" + obj[1] + "','" + obj[3] + "','" + obj[5] + "','" + obj[4] + "','" + Convert.ToInt32(Convert.ToDecimal(objprice) * 100) + "') select @@IDENTITY";
            object objRecordID = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, "select MenuId,MenuPrice from Con_Menus where MenuId in(" + obj[6] + ")");
            int count = 0;
            for (int i = 0; i < Convert.ToInt32(obj[4]); i++)
            {
                for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                {
                    count += db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, "insert into CON_RECORD_DETAIL(Record_ID,MenuId,Price) values('" + objRecordID + "','" + ds.Tables[0].Rows[j]["MenuId"] + "','" + ds.Tables[0].Rows[j]["MenuPrice"] + "')");
                }
            }
            return count;
            //int count = db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, query);
            //return count;
        }

        /// <summary>
        /// 根据编号获取一条手工录入信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet GetManualEntryInfoOne(string id)
        {
            sqldb db = new sqldb();
            string query = "select * from V_CON_INPUT_VIEW_NEW_WC where ID = '" + id + "'";
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
        }
        /// <summary>
        /// 根据RecordId编号获取RecordDetail表中的菜单信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet GetCon_Menus(string id)
        {
            sqldb db = new sqldb();
            string query = "select distinct(b.MenuId),b.MenuName from CON_RECORD_DETAIL a inner join Con_Menus b on a.MenuId=b.MenuId where Record_ID='" + id + "'";
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
        }

        /// <summary>
        /// 保存(修改界面下)
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int SaveManualEntryEdit(object[] obj)
        {
            sqldb db = new sqldb();

            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, "select MenuId,MenuPrice from Con_Menus where MenuId in(" + obj[1] + ")");
            db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, "delete from CON_RECORD_DETAIL where Record_ID='" + obj[0] + "'");
            object objprice = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, "select SUM(MenuPrice) from Con_Menus where MenuId in(" + obj[1] + ")");
            decimal dconmoney = Convert.ToDecimal(obj[2]) * Convert.ToDecimal(objprice);
            db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, "update Con_Record set Count='" + obj[2] + "',Price='" + Convert.ToInt32(Convert.ToDecimal(objprice) * 100) + "',ConMoney='" + Convert.ToInt32(dconmoney * 100) + "' where ID='" + obj[0] + "'");
            
            int rowcount = 0;
            for (int i = 0; i < Convert.ToInt32(obj[2]); i++)
            {
                for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                {
                    rowcount += db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, "insert into CON_RECORD_DETAIL(Record_ID,MenuId,Price) values('" + obj[0] + "','" + ds.Tables[0].Rows[j]["MenuId"] + "','" + ds.Tables[0].Rows[j]["MenuPrice"] + "')");
                }
            }
            return rowcount;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteManualEntry(string id)
        {
            sqldb db = new sqldb();
            string query = "delete from CON_RECORD_DETAIL where Record_ID='" + id + "'";
            db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, query);
            query = "Delete from Con_Record where ID = '" + id + "'";
            int rowcount = db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, query);
            return rowcount;
        }

        public DataSet SelLeftBox(string strquery)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select * from Con_Menus where 1=1 " + strquery;
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }
    }
}
